﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class ImportDeclarationsDetailDA : IImportDeclarationsDetailDA
    {
        private string ALT_SKU_ID = "@ALT_SKU_ID";
        private string CASE_ID = "@CASE_ID";
        private string CREATED_BY = "@CREATED_BY";
        private string CUBE = "@CUBE";
        private string DECLARATIONS_ID = "@DECLARATIONS_ID";
        private string EXTENDED_PRICE = "@EXTENDED_PRICE";
        private string GROSS_WGT = "@GROSS_WGT";
        private string ITEM_NO_DESCR = "@ITEM_NO_DESCR";
        private string ITEM_NO_ID = "@ITEM_NO_ID";
        private string LINE_ID = "@LINE_ID";
        private string NET_WGT = "@NET_WGT";
        private string OTHER_UNIT1 = "@OTHER_UNIT1";
        private string OTHER_UNIT2 = "@OTHER_UNIT2";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string QTY = "@QTY";
        private string RECEIPT_ID = "@RECEIPT_ID";
        private string RECEIPT_LINE_ID = "@RECEIPT_LINE_ID";
        private string REMARK = "@REMARK";
        private string SKU_DESCR = "@SKU_DESCR";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_IMPORT_DECLARATIONS_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@IMPORT_DECLARATIONS_DETAIL WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_IMPORT_DECLARATIONS_DETAIL = " DELETE FROM @_@IMPORT_DECLARATIONS_DETAIL WHERE DECLARATIONS_ID = @DECLARATIONS_ID";
        private string SQL_INSERT_IMPORT_DECLARATIONS_DETAIL = " INSERT INTO @_@IMPORT_DECLARATIONS_DETAIL ( WH_ID, DECLARATIONS_ID, LINE_ID, RECEIPT_ID, RECEIPT_LINE_ID, OWNER_ID, SKU_ID, ALT_SKU_ID, CASE_ID, STATUS, UOM_ID, PACK_ID, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, UNIT_PRICE, EXTENDED_PRICE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, QTY,ITEM_NO_ID) VALUES ( @WH_ID, @DECLARATIONS_ID, @LINE_ID, @RECEIPT_ID, @RECEIPT_LINE_ID, @OWNER_ID, @SKU_ID, @ALT_SKU_ID, @CASE_ID, @STATUS, @UOM_ID, @PACK_ID, @CUBE, @GROSS_WGT, @NET_WGT, @OTHER_UNIT1, @OTHER_UNIT2, @UNIT_PRICE, @EXTENDED_PRICE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(), @QTY, @ITEM_NO_ID)  ";
        private string SQL_SELECT_ALL_IMPORT_DECLARATIONS_DETAIL = " SELECT  IDD.ROWID, IDD.WH_ID, IDD.DECLARATIONS_ID, IDD.LINE_ID, IDD.RECEIPT_ID, IDD.RECEIPT_LINE_ID, IDD.OWNER_ID, IDD.SKU_ID, IDD.ALT_SKU_ID, IDD.CASE_ID, IDD.STATUS, IDD.UOM_ID, IDD.PACK_ID, IDD.CUBE, IDD.GROSS_WGT,IDD.NET_WGT, IDD.OTHER_UNIT1, IDD.OTHER_UNIT2, IDD.UNIT_PRICE, IDD.EXTENDED_PRICE, IDD.UDF1, IDD.UDF2, IDD.UDF3, IDD.UDF4,IDD.UDF5, IDD.UDF6, IDD.UDF7, IDD.UDF8, IDD.UDF9, IDD.UDF10, IDD.REMARK, IDD.TS, IDD.CREATED_BY, IDD.CREATED_DATE, IDD.UPDATED_BY, IDD.UPDATED_DATE, IDD.QTY,S.DESCR AS SKU_DESCR,ITEM.DESCR  AS ITEM_NO_DESCR,IDD.ITEM_NO_ID  FROM  @_@IMPORT_DECLARATIONS_DETAIL IDD  LEFT JOIN @_@SKU S ON IDD.OWNER_ID=S.OWNER_ID AND IDD.SKU_ID=S.SKU_ID LEFT JOIN @_@ITEM_NO ITEM ON IDD.ITEM_NO_ID=ITEM.ITEM_NO_ID";
        private string SQL_UPDATE_IMPORT_DECLARATIONS_DETAIL = " UPDATE @_@IMPORT_DECLARATIONS_DETAIL SET WH_ID = @WH_ID, RECEIPT_ID = @RECEIPT_ID, RECEIPT_LINE_ID = @RECEIPT_LINE_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, ALT_SKU_ID = @ALT_SKU_ID, CASE_ID = @CASE_ID, STATUS = @STATUS, UOM_ID = @UOM_ID, PACK_ID = @PACK_ID, CUBE = @CUBE, GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT, OTHER_UNIT1 = @OTHER_UNIT1, OTHER_UNIT2 = @OTHER_UNIT2, UNIT_PRICE = @UNIT_PRICE, EXTENDED_PRICE = @EXTENDED_PRICE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), QTY = @QTY,ITEM_NO_ID=@ITEM_NO_ID WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID  ";
        private string SQL_UPDATE_IMPORT_DECLARATIONS_DETAIL_STATUS = " UPDATE @_@IMPORT_DECLARATIONS_DETAIL SET  STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE DECLARATIONS_ID = @DECLARATIONS_ID  ";
        private string STATUS = "@STATUS";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UNIT_PRICE = "@UNIT_PRICE";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckImportDeclarationsDetailIDUnique(string importDeclarationsDetailID, string LineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", importDeclarationsDetailID), new SqlParameter("@LINE_ID", LineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_IMPORT_DECLARATIONS_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckImportDeclarationsDetailIDUnique(DataBase dataBase, DbTransaction tran, string importDeclarationsDetailID, string LineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID;_LINE_ID", importDeclarationsDetailID), new SqlParameter("@LINE_ID", LineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_IMPORT_DECLARATIONS_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteImportDeclarationsDetail(List<string> importDeclarationsDetailID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < importDeclarationsDetailID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", importDeclarationsDetailID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteImportDeclarationsDetail(DataBase dataBase, DbTransaction tran, List<string> importDeclarationsDetailID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < importDeclarationsDetailID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", importDeclarationsDetailID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllImportDeclarationsDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllImportDeclarationsDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<ImportDeclarationsDetailInfo> GetImportDeclarationsDetail(DataBase dataBase, DbTransaction tran, string declarationsID, string skuID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT IDD.ROWID, IDD.WH_ID, IDD.DECLARATIONS_ID, IDD.LINE_ID, IDD.RECEIPT_ID, IDD.RECEIPT_LINE_ID, IDD.OWNER_ID, IDD.SKU_ID, IDD.ALT_SKU_ID, IDD.CASE_ID, IDD.STATUS, IDD.UOM_ID, IDD.PACK_ID, IDD.CUBE, IDD.GROSS_WGT,IDD.NET_WGT, IDD.OTHER_UNIT1, IDD.OTHER_UNIT2, IDD.UNIT_PRICE, IDD.EXTENDED_PRICE, IDD.UDF1, IDD.UDF2, IDD.UDF3, IDD.UDF4,IDD.UDF5, IDD.UDF6, IDD.UDF7, IDD.UDF8, IDD.UDF9, IDD.UDF10, IDD.REMARK, IDD.TS, IDD.CREATED_BY, IDD.CREATED_DATE, IDD.UPDATED_BY, IDD.UPDATED_DATE, IDD.QTY,IDD.ITEM_NO_ID");
            strSql.Append("   FROM  @_@IMPORT_DECLARATIONS_DETAIL IDD ");
            strSql.Append("    WHERE IDD.DECLARATIONS_ID = @DECLARATIONS_ID AND IDD.SKU_ID=@SKU_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", declarationsID), new SqlParameter("@SKU_ID", skuID) };
            List<ImportDeclarationsDetailInfo> importDeclarationsDetailList = new List<ImportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ImportDeclarationsDetailInfo importDeclarationsDetailInfo = new ImportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, "", "", "", 0M, 0M);
                importDeclarationsDetailList.Add(importDeclarationsDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return importDeclarationsDetailList;
        }

        public List<ImportDeclarationsDetailInfo> GetImportDeclarationsDetailByID(string importdeclarationsdetailID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT IDD.ROWID, IDD.WH_ID, IDD.DECLARATIONS_ID, IDD.LINE_ID, IDD.RECEIPT_ID, IDD.RECEIPT_LINE_ID, IDD.OWNER_ID, IDD.SKU_ID, IDD.ALT_SKU_ID, IDD.CASE_ID, IDD.STATUS, IDD.UOM_ID, IDD.PACK_ID, IDD.CUBE, IDD.GROSS_WGT,IDD.NET_WGT, IDD.OTHER_UNIT1, IDD.OTHER_UNIT2, IDD.UNIT_PRICE, IDD.EXTENDED_PRICE, IDD.UDF1, IDD.UDF2, IDD.UDF3, IDD.UDF4,IDD.UDF5, IDD.UDF6, IDD.UDF7, IDD.UDF8, IDD.UDF9, IDD.UDF10, IDD.REMARK, IDD.TS, IDD.CREATED_BY, IDD.CREATED_DATE, IDD.UPDATED_BY, IDD.UPDATED_DATE, IDD.QTY,IDD.ITEM_NO_ID");
            strSql.Append("   FROM  @_@IMPORT_DECLARATIONS_DETAIL IDD ");
            strSql.Append("    WHERE IDD.DECLARATIONS_ID = @DECLARATIONS_ID");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", importdeclarationsdetailID) };
            List<ImportDeclarationsDetailInfo> importDeclarationsDetailList = new List<ImportDeclarationsDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    ImportDeclarationsDetailInfo importDeclarationsDetailInfo = new ImportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, "", "", "", 0M, 0M);
                    importDeclarationsDetailList.Add(importDeclarationsDetailInfo);
                }
            }
            return importDeclarationsDetailList;
        }

        public List<ImportDeclarationsDetailInfo> GetImportDeclarationsDetailByID(DataBase dataBase, DbTransaction tran, string importdeclarationsdetailID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT IDD.ROWID, IDD.WH_ID, IDD.DECLARATIONS_ID, IDD.LINE_ID, IDD.RECEIPT_ID, IDD.RECEIPT_LINE_ID, IDD.OWNER_ID, IDD.SKU_ID, IDD.ALT_SKU_ID, IDD.CASE_ID, IDD.STATUS, IDD.UOM_ID, IDD.PACK_ID, IDD.CUBE, IDD.GROSS_WGT,IDD.NET_WGT, IDD.OTHER_UNIT1, IDD.OTHER_UNIT2, IDD.UNIT_PRICE, IDD.EXTENDED_PRICE, IDD.UDF1, IDD.UDF2, IDD.UDF3, IDD.UDF4,IDD.UDF5, IDD.UDF6, IDD.UDF7, IDD.UDF8, IDD.UDF9, IDD.UDF10, IDD.REMARK, IDD.TS, IDD.CREATED_BY, IDD.CREATED_DATE, IDD.UPDATED_BY, IDD.UPDATED_DATE, IDD.QTY,IDD.ITEM_NO_ID");
            strSql.Append("   FROM  @_@IMPORT_DECLARATIONS_DETAIL IDD ");
            strSql.Append("    WHERE IDD.DECLARATIONS_ID = @DECLARATIONS_ID");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", importdeclarationsdetailID) };
            List<ImportDeclarationsDetailInfo> importDeclarationsDetailList = new List<ImportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ImportDeclarationsDetailInfo importDeclarationsDetailInfo = new ImportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, "", "", "", 0M, 0M);
                importDeclarationsDetailList.Add(importDeclarationsDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return importDeclarationsDetailList;
        }

        public DataSet GetImportDeclarationsDetailByQueryList(List<string> sqlWhere, ImportDeclarationsDetailQueryEntity importdeclarationsdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!importdeclarationsdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, importdeclarationsdetailQuery.CurrentPage, importdeclarationsdetailQuery.PageSize, importdeclarationsdetailQuery.SortField, importdeclarationsdetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetImportDeclarationsDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, ImportDeclarationsDetailQueryEntity importdeclarationsdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, importdeclarationsdetailQuery.CurrentPage, importdeclarationsdetailQuery.PageSize, importdeclarationsdetailQuery.SortField, importdeclarationsdetailQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<ImportDeclarationsDetailInfo> GetImportDeclarationsDetailGroupBy(string importdeclarationsdetailID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT  DECLARATIONS_ID, OWNER_ID, SKU_ID,UOM_ID,SUM(CONVERT(FLOAT,ISNULL(UDF1,'0'))) AS UDF1,SUM(CONVERT(FLOAT,ISNULL(UDF2,'0'))) AS UDF2,SUM(CUBE) AS CUBE ,SUM(GROSS_WGT) AS GROSS_WGT,SUM(NET_WGT) AS NET_WGT,UNIT_PRICE,SUM(EXTENDED_PRICE) AS EXTENDED_PRICE,  REMARK,SUM(QTY) AS QTY,SKU_DESCR, ITEM_NO_DESCR,ITEM_NO_ID,SUM(RECEIVED_QTY) AS RECEIVED_QTY ,SUM(DIFFERENCES_QTY) AS DIFFERENCES_QTY  ");
            strSql.Append("   FROM  (SELECT IDD.DECLARATIONS_ID,IDD.OWNER_ID, IDD.SKU_ID,IDD.UOM_ID, IDD.CUBE ,IDD.UDF1,IDD.UDF2,IDD.GROSS_WGT,IDD.NET_WGT,IDD.UNIT_PRICE,IDD.EXTENDED_PRICE,IDD.REMARK,IDD.QTY,S.DESCR AS SKU_DESCR,ITEM.DESCR AS ITEM_NO_DESCR,IDD.ITEM_NO_ID,CASE WHEN RD.RECEIVED_QTY<>0 THEN RD.RECEIVED_QTY ELSE RDL.RECEIVED_QTY  END  AS RECEIVED_QTY,(CASE WHEN  RD.RECEIVED_QTY <> 0 THEN RD.RECEIVED_QTY- ISNULL(RD.DECLARATION_QTY,0) ELSE RDL.RECEIVED_QTY- ISNULL(RDL.DECLARATION_QTY,0)   END) AS DIFFERENCES_QTY  ");
            strSql.Append("   FROM  @_@IMPORT_DECLARATIONS_DETAIL IDD LEFT JOIN @_@RECEIPT_DETAIL RD ON IDD.RECEIPT_ID =RD.RECEIPT_ID AND IDD.RECEIPT_LINE_ID=RD.RECEIPT_LINE_ID LEFT JOIN @_@RECEIPT_DETAIL_LPN RDL ON IDD.RECEIPT_ID =RDL.RECEIPT_ID AND IDD.RECEIPT_LINE_ID=RDL.RECEIPT_LINE_ID LEFT JOIN @_@SKU S ON IDD.OWNER_ID=S.OWNER_ID AND IDD.SKU_ID=S.SKU_ID LEFT JOIN  @_@ITEM_NO ITEM ON IDD.ITEM_NO_ID=ITEM.ITEM_NO_ID WHERE IDD.DECLARATIONS_ID = @DECLARATIONS_ID ) A ");
            strSql.Append("   GROUP BY DECLARATIONS_ID, OWNER_ID, SKU_ID,UOM_ID,UNIT_PRICE,REMARK,SKU_DESCR ,ITEM_NO_DESCR,ITEM_NO_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", importdeclarationsdetailID) };
            List<ImportDeclarationsDetailInfo> importDeclarationsDetailList = new List<ImportDeclarationsDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002 = new DateTime();
                    CS00002 = new DateTime();
                    ImportDeclarationsDetailInfo importDeclarationsDetailInfo = new ImportDeclarationsDetailInfo(0, "", reader["DECLARATIONS_ID"].ToString(), 0, "", 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), "", "", "", reader["UOM_ID"].ToString(), "", (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, 0M, 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), "", "", "", "", "", "", "", "", reader["REMARK"].ToString(), new byte[0], "", CS00002, "", CS00002, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["ITEM_NO_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ITEM_NO_DESCR"].ToString(), (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["DIFFERENCES_QTY"].ToString() != "") ? decimal.Parse(reader["DIFFERENCES_QTY"].ToString()) : 0M);
                    importDeclarationsDetailList.Add(importDeclarationsDetailInfo);
                }
            }
            return importDeclarationsDetailList;
        }

        public List<ImportDeclarationsDetailInfo> GetImportDeclarationsDetailGroupBy(DataBase dataBase, DbTransaction tran, string importdeclarationsdetailID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT  DECLARATIONS_ID, OWNER_ID, SKU_ID,UOM_ID,SUM(CONVERT(FLOAT,ISNULL(UDF1,'0'))) AS UDF1,SUM(CONVERT(FLOAT,ISNULL(UDF2,'0'))) AS UDF2,SUM(CUBE) AS CUBE ,SUM(GROSS_WGT) AS GROSS_WGT,SUM(NET_WGT) AS NET_WGT,UNIT_PRICE,SUM(EXTENDED_PRICE) AS EXTENDED_PRICE,  REMARK,SUM(QTY) AS QTY,SKU_DESCR, ITEM_NO_DESCR,ITEM_NO_ID,SUM(RECEIVED_QTY) AS RECEIVED_QTY ,SUM(DIFFERENCES_QTY) AS DIFFERENCES_QTY  ");
            strSql.Append("   FROM  (SELECT IDD.DECLARATIONS_ID,IDD.OWNER_ID, IDD.SKU_ID,IDD.UOM_ID, IDD.CUBE ,IDD.UDF1,IDD.UDF2,IDD.GROSS_WGT,IDD.NET_WGT,IDD.UNIT_PRICE,IDD.EXTENDED_PRICE,IDD.REMARK,IDD.QTY,S.DESCR AS SKU_DESCR,ITEM.DESCR AS ITEM_NO_DESCR,IDD.ITEM_NO_ID,CASE WHEN RD.RECEIVED_QTY<>0 THEN RD.RECEIVED_QTY ELSE RDL.RECEIVED_QTY  END  AS RECEIVED_QTY,(CASE WHEN  RD.RECEIVED_QTY <> 0 THEN RD.RECEIVED_QTY- ISNULL(RD.DECLARATION_QTY,0) ELSE RDL.RECEIVED_QTY- ISNULL(RDL.DECLARATION_QTY,0)   END) AS DIFFERENCES_QTY  ");
            strSql.Append("   FROM  @_@IMPORT_DECLARATIONS_DETAIL IDD LEFT JOIN @_@RECEIPT_DETAIL RD ON IDD.RECEIPT_ID =RD.RECEIPT_ID AND IDD.RECEIPT_LINE_ID=RD.RECEIPT_LINE_ID LEFT JOIN @_@RECEIPT_DETAIL_LPN RDL ON IDD.RECEIPT_ID =RDL.RECEIPT_ID AND IDD.RECEIPT_LINE_ID=RDL.RECEIPT_LINE_ID LEFT JOIN @_@SKU S ON IDD.OWNER_ID=S.OWNER_ID AND IDD.SKU_ID=S.SKU_ID LEFT JOIN  @_@ITEM_NO ITEM ON IDD.ITEM_NO_ID=ITEM.ITEM_NO_ID WHERE IDD.DECLARATIONS_ID = @DECLARATIONS_ID ) A ");
            strSql.Append("   GROUP BY DECLARATIONS_ID, OWNER_ID, SKU_ID,UOM_ID,UNIT_PRICE,REMARK,SKU_DESCR ,ITEM_NO_DESCR,ITEM_NO_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", importdeclarationsdetailID) };
            List<ImportDeclarationsDetailInfo> importDeclarationsDetailList = new List<ImportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00002 = new DateTime();
                CS00002 = new DateTime();
                ImportDeclarationsDetailInfo importDeclarationsDetailInfo = new ImportDeclarationsDetailInfo(0, "", reader["DECLARATIONS_ID"].ToString(), 0, "", 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), "", "", "", reader["UOM_ID"].ToString(), "", (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, 0M, 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), "", "", "", "", "", "", "", "", reader["REMARK"].ToString(), new byte[0], "", CS00002, "", CS00002, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["ITEM_NO_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ITEM_NO_DESCR"].ToString(), (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["DIFFERENCES_QTY"].ToString() != "") ? decimal.Parse(reader["DIFFERENCES_QTY"].ToString()) : 0M);
                importDeclarationsDetailList.Add(importDeclarationsDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return importDeclarationsDetailList;
        }

        public List<ImportDeclarationsDetailInfo> GetImportDeclarationsDetailInfoByRotationRule(DataBase dataBase, DbTransaction tran, ImportDeclarationsDetailInfo importDeclarationsDetailInfo, string orderby, string whLogin)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT  IDD.ROWID, IDD.WH_ID, IDD.DECLARATIONS_ID, IDD.LINE_ID, IDD.RECEIPT_ID, IDD.RECEIPT_LINE_ID, IDD.OWNER_ID, IDD.SKU_ID, IDD.ALT_SKU_ID, IDD.CASE_ID, IDD.STATUS, IDD.UOM_ID, IDD.PACK_ID, IDD.CUBE, IDD.GROSS_WGT,IDD.NET_WGT, IDD.OTHER_UNIT1, IDD.OTHER_UNIT2, IDD.UNIT_PRICE, IDD.EXTENDED_PRICE, IDD.UDF1, IDD.UDF2, IDD.UDF3, IDD.UDF4,IDD.UDF5, IDD.UDF6, IDD.UDF7, IDD.UDF8, IDD.UDF9, IDD.UDF10, IDD.REMARK, IDD.TS, IDD.CREATED_BY, IDD.CREATED_DATE, IDD.UPDATED_BY, IDD.UPDATED_DATE, IDD.QTY,IDD.ITEM_NO_ID,CASE WHEN  RD.RECEIVED_QTY <> 0 THEN RD.RECEIVED_QTY  ELSE RDL.RECEIVED_QTY  END AS RECEIVED_QTY,CASE WHEN  RD.RECEIVED_QTY <> 0 THEN RD.RECEIVED_QTY- ISNULL(RD.DECLARATION_QTY,0) ELSE RDL.RECEIVED_QTY- ISNULL(RDL.DECLARATION_QTY,0) END AS DIFFERENCES_QTY ");
            strSql.Append("   FROM  @_@IMPORT_DECLARATIONS_DETAIL IDD  ");
            strSql.Append("   LEFT JOIN @_@RECEIPT_DETAIL RD ON IDD.RECEIPT_ID =RD.RECEIPT_ID AND IDD.RECEIPT_LINE_ID=RD.RECEIPT_LINE_ID  LEFT JOIN @_@RECEIPT_DETAIL_LPN RDL ON IDD.RECEIPT_ID =RDL.RECEIPT_ID AND IDD.RECEIPT_LINE_ID=RDL.RECEIPT_LINE_ID   LEFT JOIN  @_@INV_TRANS IT  ON IDD.RECEIPT_ID=IT.DOC_ID AND IDD.RECEIPT_LINE_ID=IT.DOC_LINE_ID AND  IT.TRANS_TYPE='IN' AND IT.SOURCE_TRANS_TYPE='RECEIPTDETAILADD' AND IT.STATUS='OK' ");
            strSql.Append("   WHERE IDD.DECLARATIONS_ID = @DECLARATIONS_ID AND IDD.OWNER_ID=@OWNER_ID AND IDD.SKU_ID=@SKU_ID AND IDD.UOM_ID=@UOM_ID AND IDD.UNIT_PRICE= @UNIT_PRICE  ");
            if (string.IsNullOrEmpty(importDeclarationsDetailInfo.Remark))
            {
                strSql.Append("   AND (IDD.REMARK IS NULL  OR IDD.REMARK ='') ");
            }
            else
            {
                strSql.Append("   AND IDD.REMARK  =@REMARK");
            }
            if (string.IsNullOrEmpty(importDeclarationsDetailInfo.ItemNoID))
            {
                strSql.Append("   AND (IDD.ITEM_NO_ID IS NULL OR  IDD.ITEM_NO_ID ='') ");
            }
            else
            {
                strSql.Append("   AND IDD.ITEM_NO_ID  =@ITEM_NO_ID");
            }
            strSql.AppendFormat("  {0}", orderby);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", importDeclarationsDetailInfo.DeclarationsID), new SqlParameter("@OWNER_ID", importDeclarationsDetailInfo.OwnerID), new SqlParameter("@SKU_ID", importDeclarationsDetailInfo.SkuID), new SqlParameter("@UOM_ID", importDeclarationsDetailInfo.UomID), new SqlParameter("@UNIT_PRICE", importDeclarationsDetailInfo.UnitPrice), new SqlParameter("@REMARK", importDeclarationsDetailInfo.Remark), new SqlParameter("@ITEM_NO_ID", importDeclarationsDetailInfo.ItemNoID) };
            List<ImportDeclarationsDetailInfo> importDeclarationsDetailList = new List<ImportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLogin).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00003;
                ImportDeclarationsDetailInfo importDeclarationsDetailsInfo = new ImportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["ITEM_NO_ID"].ToString(), "", "", (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["DIFFERENCES_QTY"].ToString() != "") ? decimal.Parse(reader["DIFFERENCES_QTY"].ToString()) : 0M);
                importDeclarationsDetailList.Add(importDeclarationsDetailsInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return importDeclarationsDetailList;
        }

        public List<ImportDeclarationsDetailInfo> GetImportDeclarationsDetailReceiptIDLineID(DataBase dataBase, DbTransaction tran, string receiptID, string lineID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT IDD.ROWID, IDD.WH_ID, IDD.DECLARATIONS_ID, IDD.LINE_ID, IDD.RECEIPT_ID, IDD.RECEIPT_LINE_ID, IDD.OWNER_ID, IDD.SKU_ID, IDD.ALT_SKU_ID, IDD.CASE_ID, IDD.STATUS, IDD.UOM_ID, IDD.PACK_ID, IDD.CUBE, IDD.GROSS_WGT,IDD.NET_WGT, IDD.OTHER_UNIT1, IDD.OTHER_UNIT2, IDD.UNIT_PRICE, IDD.EXTENDED_PRICE, IDD.UDF1, IDD.UDF2, IDD.UDF3, IDD.UDF4,IDD.UDF5, IDD.UDF6, IDD.UDF7, IDD.UDF8, IDD.UDF9, IDD.UDF10, IDD.REMARK, IDD.TS, IDD.CREATED_BY, IDD.CREATED_DATE, IDD.UPDATED_BY, IDD.UPDATED_DATE, IDD.QTY,IDD.ITEM_NO_ID");
            strSql.Append("   FROM  @_@IMPORT_DECLARATIONS_DETAIL IDD ");
            strSql.Append("    WHERE IDD.RECEIPT_ID = @RECEIPT_ID AND IDD.RECEIPT_LINE_ID=@RECEIPT_LINE_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", lineID) };
            List<ImportDeclarationsDetailInfo> importDeclarationsDetailList = new List<ImportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ImportDeclarationsDetailInfo importDeclarationsDetailInfo = new ImportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, "", "", "", 0M, 0M);
                importDeclarationsDetailList.Add(importDeclarationsDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return importDeclarationsDetailList;
        }

        public int InsertImportDeclarationsDetail(ImportDeclarationsDetailInfo importDeclarationsDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                importDeclarationsDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ImportDeclarationsDetail_Parameters(importDeclarationsDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertImportDeclarationsDetail(DataBase dataBase, DbTransaction tran, ImportDeclarationsDetailInfo importDeclarationsDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                importDeclarationsDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ImportDeclarationsDetail_Parameters(importDeclarationsDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_ImportDeclarationsDetail_Parameters(ImportDeclarationsDetailInfo importDeclarationsDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DECLARATIONS_ID, SqlDbType.VarChar, 30), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CASE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CUBE, SqlDbType.Decimal, 13), new SqlParameter(this.GROSS_WGT, SqlDbType.Decimal, 13), new SqlParameter(this.NET_WGT, SqlDbType.Decimal, 13), new SqlParameter(this.OTHER_UNIT1, SqlDbType.Decimal, 13), 
                new SqlParameter(this.OTHER_UNIT2, SqlDbType.Decimal, 13), new SqlParameter(this.UNIT_PRICE, SqlDbType.Decimal, 13), new SqlParameter(this.EXTENDED_PRICE, SqlDbType.Decimal, 13), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), 
                new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ITEM_NO_ID, SqlDbType.VarChar, 50)
             };
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.WhID))
            {
                paramArray[0].Value = importDeclarationsDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = importDeclarationsDetailInfo.DeclarationsID;
            paramArray[2].Value = importDeclarationsDetailInfo.LineID;
            paramArray[3].Value = importDeclarationsDetailInfo.ReceiptID;
            paramArray[4].Value = importDeclarationsDetailInfo.ReceiptLineID;
            paramArray[5].Value = importDeclarationsDetailInfo.OwnerID;
            paramArray[6].Value = importDeclarationsDetailInfo.SkuID;
            paramArray[7].Value = importDeclarationsDetailInfo.AltSkuID;
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.CaseID))
            {
                paramArray[8].Value = importDeclarationsDetailInfo.CaseID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = importDeclarationsDetailInfo.Status;
            paramArray[10].Value = importDeclarationsDetailInfo.UomID;
            paramArray[11].Value = importDeclarationsDetailInfo.PackID;
            paramArray[12].Value = importDeclarationsDetailInfo.Cube;
            paramArray[13].Value = importDeclarationsDetailInfo.GrossWgt;
            paramArray[14].Value = importDeclarationsDetailInfo.NetWgt;
            paramArray[15].Value = importDeclarationsDetailInfo.OtherUnit1;
            paramArray[0x10].Value = importDeclarationsDetailInfo.OtherUnit2;
            paramArray[0x11].Value = importDeclarationsDetailInfo.UnitPrice;
            paramArray[0x12].Value = importDeclarationsDetailInfo.ExtendedPrice;
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf1))
            {
                paramArray[0x13].Value = importDeclarationsDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf2))
            {
                paramArray[20].Value = importDeclarationsDetailInfo.Udf2;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf3))
            {
                paramArray[0x15].Value = importDeclarationsDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf4))
            {
                paramArray[0x16].Value = importDeclarationsDetailInfo.Udf4;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf5))
            {
                paramArray[0x17].Value = importDeclarationsDetailInfo.Udf5;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf6))
            {
                paramArray[0x18].Value = importDeclarationsDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf7))
            {
                paramArray[0x19].Value = importDeclarationsDetailInfo.Udf7;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf8))
            {
                paramArray[0x1a].Value = importDeclarationsDetailInfo.Udf8;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf9))
            {
                paramArray[0x1b].Value = importDeclarationsDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Udf10))
            {
                paramArray[0x1c].Value = importDeclarationsDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(importDeclarationsDetailInfo.Remark))
            {
                paramArray[0x1d].Value = importDeclarationsDetailInfo.Remark;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            paramArray[30].Value = importDeclarationsDetailInfo.CreatedBy;
            paramArray[0x1f].Value = importDeclarationsDetailInfo.UpdatedBy;
            paramArray[0x20].Value = importDeclarationsDetailInfo.Qty;
            paramArray[0x21].Value = importDeclarationsDetailInfo.ItemNoID;
            return paramArray;
        }

        public int UpdateImportDeclarationsDetail(ImportDeclarationsDetailInfo importDeclarationsDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                importDeclarationsDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ImportDeclarationsDetail_Parameters(importDeclarationsDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateImportDeclarationsDetail(DataBase dataBase, DbTransaction tran, ImportDeclarationsDetailInfo importDeclarationsDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                importDeclarationsDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ImportDeclarationsDetail_Parameters(importDeclarationsDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_IMPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateImportDeclarationsDetailByDeclarationsID(DataBase dataBase, DbTransaction tran, ImportDeclarationsDetailInfo info, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = " UPDATE @_@IMPORT_DECLARATIONS_DETAIL SET UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT, CUBE = @CUBE, UNIT_PRICE = @UNIT_PRICE, EXTENDED_PRICE = @EXTENDED_PRICE, UDF1 = @UDF1, UDF2 = @UDF2 WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND SKU_ID = @SKU_ID AND LINE_ID=@LINE_ID";
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", info.DeclarationsID), new SqlParameter("@LINE_ID", info.LineID), new SqlParameter("@SKU_ID", info.SkuID), new SqlParameter("@GROSS_WGT", info.GrossWgt), new SqlParameter("@NET_WGT", info.NetWgt), new SqlParameter("@CUBE", info.Cube), new SqlParameter("@UNIT_PRICE", info.UnitPrice), new SqlParameter("@EXTENDED_PRICE", info.ExtendedPrice), new SqlParameter("@UDF1", info.Udf1), new SqlParameter("@UDF2", info.Udf2), new SqlParameter("@UPDATED_BY", updateBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateImportDeclarationsDetailQtyByDeclarationsID(DataBase dataBase, DbTransaction tran, string DeclarationsID, string LineID, decimal qty, decimal extendedPrice, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = " UPDATE @_@IMPORT_DECLARATIONS_DETAIL SET UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), QTY = @QTY,EXTENDED_PRICE = @EXTENDED_PRICE WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID ";
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", DeclarationsID), new SqlParameter("@LINE_ID", LineID), new SqlParameter("@QTY", qty), new SqlParameter("@EXTENDED_PRICE", extendedPrice), new SqlParameter("@UPDATED_BY", updateBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateImportDeclarationsDetailStatusByID(DataBase dataBase, DbTransaction tran, string declarationsID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", declarationsID), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_IMPORT_DECLARATIONS_DETAIL_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

